package br.com.wai2008.context.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLDataException;
import java.sql.SQLException;
import br.com.wai2008.context.constants.DAOConstants;

/**
 * This class implements the Information database access through DAO.
 * 
 * @since 
 * 		October, 2008.
 * 
 * @version
 * 		WAI2008 - version 1.0
 * 
 * @author 
 * 		Andre Romero Goncales
 */
public class ContextInformationDAO {

	/**
	 * Get the context information from a specific node.<br>
	 * 
	 * @param
	 * 		name the node's name.
	 * @param
	 * 		language the language of the message in the database. The Information
	 * 		database supports the following languages:<br>
	 * 		"PtBr" - portuguese Brazil.<br>
	 * 		"En" - English.<br>
	 * 		"Es" - Spanish.
	 * 
	 * @return
	 * 		The context information or null if there isn't.
	 * 
	 * @throws
	 * 		SQLException if a database access error occurs.
	 */
	public String getContextInformation(String x, String y, String language) throws SQLException {
		language = "context" + language;
		String information = null;
		String vertexId = null;
		
		Connection connection = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			connection = this.getConnection();
			stmt = connection.prepareStatement("SELECT vertexId FROM Vertex WHERE x=? AND y=?");
			stmt.setString(1, x);
			stmt.setString(2, y);
			
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				vertexId = rs.getString(1);
			}
		} catch (SQLException sqle) {
			throw sqle;
		} finally {
			rs.close();
			stmt.close();
			connection.close();
		}
		
		if (vertexId == null) {
			throw new SQLDataException();
		}
		
		connection = null;
		stmt = null;
		rs = null;
		try {
			connection = this.getConnection();
			stmt = connection.prepareStatement("SELECT " + language + " FROM Information WHERE vertexId=?");
			stmt.setString(1, vertexId);
			
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				information = rs.getString(1);
			}
		} catch (SQLException sqle) {
			throw sqle;
		} finally {
			rs.close();
			stmt.close();
			connection.close();
		}
		
		return information;
	}

	/**
	 * Get a connection to the database.<br>
	 * In order to get a connection, you must provide the URL, user and password
	 * to connect to the database.
	 * 
	 * @see
	 * 		br.com.wai2008.context.constants.DAOConstants
	 * 
	 * @throws
	 * 		SQLException if an error occur while create connection
	 * 		to the database.
	 */
	private Connection getConnection() throws SQLException {
		return DriverManager.getConnection(
				DAOConstants.DATABASE_URL, DAOConstants.DATABASE_USER,
				DAOConstants.DATABASE_PASSWORD);
	}
	
	public static void main (String[] args) throws SQLException {
		ContextInformationDAO ciDAO = new ContextInformationDAO();
		String retorno = ciDAO.getContextInformation("256", "581", "PtBr");
		System.out.println(retorno);
	}
}
